﻿using System;
using System.Collections.Generic;
using System.Transactions;
using System.Data.Objects;
using System.Linq;

namespace ConsoleApplication1
{
    class CompanyDAL
    {
        public List<EmployeesByCountry> GetEmployeesByCountry()
        {
            CompanyDatabaseEntities context = new CompanyDatabaseEntities();
            List<EmployeesByCountry> employees = new List<EmployeesByCountry>();
            using (context)
            {
                foreach (var item in context.EmployesFromCountries)
                {
                    Console.WriteLine("Number of employees in {0}: {1}", item.CountryName, item.Number_Of_Cusotmers);
                    employees.Add(new EmployeesByCountry(item.CountryName, (int)item.Number_Of_Cusotmers));
                }
            }
            return employees;
        }

        public List<CustomersByCountry> GetCustomersByCountry() 
        {
            CompanyDatabaseEntities context = new CompanyDatabaseEntities();
            List<CustomersByCountry> customers = new List<CustomersByCountry>();
            using (context) 
            {
                foreach (var item in context.CustomersFromCountries) 
                {
                    Console.WriteLine("Number of customers in {0}: {1}", item.CountryName, item.Number_Of_Cusotmers);
                    customers.Add(new CustomersByCountry(item.CountryName, (int)item.Number_Of_Cusotmers));
                }
            }
            return customers;
        }

        public void UpdateSalaryByCountry(int? countryId, decimal? raise) 
        {
            //http://msdn.microsoft.com/en-us/library/bb896231.aspx -> kak da se inportne procedurata ot bazata danni

            CompanyDatabaseEntities context = new CompanyDatabaseEntities();
            context.usp_RaiseSalary(countryId, raise);
        }

        public void FireYoungEmployees() 
        {
            //ebati depresirashtata zadacha!!!
            CompanyDatabaseEntities context = new CompanyDatabaseEntities();
            DateTime currentDate = DateTime.Now;
            DateTime startDate = new DateTime(currentDate.Year - 1, currentDate.Month, currentDate.Day);
            using(context)
            {
                using (TransactionScope scope = new TransactionScope()) 
                {
                    string nativeQuery = "Select Count(*) From Employee Where HireDate >= '" +
                        startDate.Year.ToString() + "/" + startDate.Month.ToString() + "/" +  
                        startDate.Day.ToString()+"'";
                    ObjectResult<int> result = context.ExecuteStoreQuery<int>(nativeQuery);
                    int employeesToFire = result.FirstOrDefault();
                    nativeQuery = "Select Count(*) From Employee";
                    result = context.ExecuteStoreQuery<int>(nativeQuery);
                    int allEmployees = result.FirstOrDefault();
                    if (employeesToFire > (allEmployees / 2))
                    {
                        throw new Exception("To many employees to fire");
                    }
                    else 
                    {
                        nativeQuery = "Select PersonId From Employee Where HireDate >= '" +
                            startDate.Year.ToString() + "/" + startDate.Month.ToString() + "/" +
                            startDate.Day.ToString() + "'";
                        var employeIds = context.ExecuteStoreQuery<int>(nativeQuery);
                        foreach (var id in employeIds)
                        {
                            Person personToFire = GetPersonById(id, context);
                            context.Persons.DeleteObject(personToFire);
                        }
                        context.SaveChanges();
                        scope.Complete();
                    }
                }
            }
        }
  
        private Person GetPersonById(int item, CompanyDatabaseEntities context)
        {
            Person person = context.Persons.First(p => p.PersonId == item);
            return person;
        }
    }
}

